package vn.com.ltv.lo.daos;

import java.util.ArrayList;

import vn.com.ltv.lo.dtos.CommentDTO;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class CommentDAO {
	protected static final String TABLE_NAME = "Comment";
	protected static final String ID = "rowid";
	protected static final String CMT_ID = "cmt_id";
	protected static final String CMT_DESC = "cmt_desc";
	protected static final String USR_ID = "usr_id";
	protected static final String MN_ID = "mn_id";
	protected static String TABLE_CREATION_STRING = "create table "
			+ TABLE_NAME + " (" + ID + " integer primary key autoincrement, "
			+ CMT_ID + " text, " + CMT_DESC + " text, " + USR_ID + " text, "
			+ MN_ID + " text);";
	protected static String TABLE_DROP_STRING = "drop table if exists"
			+ TABLE_NAME + ";";
	private Context context;

	public CommentDAO(Context context) {
		this.context = context;
	}

	public ArrayList<CommentDTO> getAll() {
		ArrayList<CommentDTO> commentDTOs = new ArrayList<CommentDTO>();
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getReadableDatabase();
		Cursor cursor = sQLiteDatabase.query(TABLE_NAME, null, null, null,
				null, null, null);
		if (cursor.moveToFirst()) {
			while (!cursor.isAfterLast()) {
				CommentDTO commentDTO = this.cursorToCommentDTO(cursor);
				commentDTOs.add(commentDTO);
				cursor.moveToNext();
			}
		}
		cursor.close();
		sQLiteDatabase.close();
		databaseHelper.close();
		return commentDTOs;
	}

	public CommentDTO get(int id) {
		CommentDTO commentDTO = null;
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getReadableDatabase();
		Cursor cursor = sQLiteDatabase.query(TABLE_NAME, null, ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null);
		if (cursor.moveToFirst()) {
			commentDTO = this.cursorToCommentDTO(cursor);
		}
		cursor.close();
		sQLiteDatabase.close();
		databaseHelper.close();
		return commentDTO;
	}
	
	public ArrayList<CommentDTO> getListCommentByMenuId(String menuId) {
		ArrayList<CommentDTO> listComment = new ArrayList<CommentDTO>();
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getReadableDatabase();
		String query = "SELECT cmt_id, cmt_desc, usr_id,mn_id  FROM Comment WHERE mn_id = '"+menuId+"'";
		Cursor cursor = sQLiteDatabase.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			while (!cursor.isAfterLast()) {
				CommentDTO temp = new CommentDTO();
				
				String cmtId = cursor.getString(cursor.getColumnIndex("cmt_id"));
				temp.setCmtId(cmtId);
				String cmtDesc = cursor.getString(cursor.getColumnIndex("cmt_desc"));
				temp.setCmtDesc(cmtDesc);
				String usrId = cursor.getString(cursor.getColumnIndex("usr_id"));
				temp.setUsrId(usrId);
				String mnId = cursor.getString(cursor.getColumnIndex("mn_id"));
				temp.setMnId(mnId);
				listComment.add(temp);
				cursor.moveToNext();
			}
		}
		cursor.close();
		sQLiteDatabase.close();
		databaseHelper.close();
		return listComment;
	}

	public void insert(CommentDTO commentDTO) {
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getWritableDatabase();
		ContentValues contentValues = this
				.commentDTOToContentValues(commentDTO);
		sQLiteDatabase.insert(TABLE_NAME, null, contentValues);
		sQLiteDatabase.close();
		databaseHelper.close();
	}

	/** * @return the number of rows affected */
	public int update(CommentDTO commentDTO) {
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getWritableDatabase();
		int id = commentDTO.getId();
		commentDTO.setId(-1);
		ContentValues contentValues = this
				.commentDTOToContentValues(commentDTO);
		int rowCount = sQLiteDatabase.update(TABLE_NAME, contentValues, ID
				+ "=?", new String[] { String.valueOf(id) });
		sQLiteDatabase.close();
		databaseHelper.close();
		return rowCount;
	}

	public void delete(int id) {
		DatabaseHelper databaseHelper = new DatabaseHelper(this.context);
		SQLiteDatabase sQLiteDatabase = databaseHelper.getWritableDatabase();
		sQLiteDatabase.delete(TABLE_NAME, ID + "=?",
				new String[] { String.valueOf(id) });
		sQLiteDatabase.close();
		databaseHelper.close();
	}

	private CommentDTO cursorToCommentDTO(Cursor cursor) {
		CommentDTO commentDTO = new CommentDTO();
		int id = cursor.getInt(cursor.getColumnIndex(ID));
		commentDTO.setId(id);
		String cmtId = cursor.getString(cursor.getColumnIndex(CMT_ID));
		commentDTO.setCmtId(cmtId);
		String cmtDesc = cursor.getString(cursor.getColumnIndex(CMT_DESC));
		commentDTO.setCmtDesc(cmtDesc);
		String usrId = cursor.getString(cursor.getColumnIndex(USR_ID));
		commentDTO.setUsrId(usrId);
		String mnId = cursor.getString(cursor.getColumnIndex(MN_ID));
		commentDTO.setMnId(mnId);
		return commentDTO;
	}

	private ContentValues commentDTOToContentValues(CommentDTO commentDTO) {
		ContentValues contentValues = new ContentValues();
		int id = commentDTO.getId();
		if (id >= 0) {
			contentValues.put(ID, id);
		}
		String cmtId = commentDTO.getCmtId();
		if (cmtId != null) {
			contentValues.put(CMT_ID, cmtId);
		}
		String cmtDesc = commentDTO.getCmtDesc();
		if (cmtDesc != null) {
			contentValues.put(CMT_DESC, cmtDesc);
		}
		String usrId = commentDTO.getUsrId();
		if (usrId != null) {
			contentValues.put(USR_ID, usrId);
		}
		String mnId = commentDTO.getMnId();
		if (mnId != null) {
			contentValues.put(MN_ID, mnId);
		}
		return contentValues;
	}
}